﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using YXShop.IDAL.Templates;
using System.Data;
using System.Data.SqlClient;

namespace YXShop.SQLServerDAL.Templates
{
    public class FreeLabel:IFreeLabel
    {
        public FreeLabel()
        {
        }

        #region Database Operation
      
        public int Add(YXShop.Model.Templates.FreeLabel model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into [yxs_freelable](");
            strSql.Append("[type_id],[free_name],[free_content],[free_description],[free_createtime],[free_labeltype],[free_labelclass],[free_labelsql] )");
            strSql.Append("values(");
            strSql.Append("@type_id,@free_name,@free_content,@free_description,@free_createtime,@free_labeltype,@free_labelclass,@free_labelsql) Select scope_IDENTITY() ");
            SqlParameter[] parameters = (SqlParameter[])this.ValueParams(model);
            int count = ChangeHope.DataBase.SQLServerHelper.ExecuteSql(strSql.ToString(),parameters);
            return count;
        }

        public void Update(YXShop.Model.Templates.FreeLabel model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update [yxs_freelable] set ");
            strSql.Append("[type_id]=@type_id,[free_name]=@free_name,[free_content]=@free_content,[free_description]=@free_description,[free_createtime]=@free_createtime,[free_labeltype]=@free_labeltype,[free_labelclass]=@free_labelclass,[free_labelsql]=@free_labelsql ");
            strSql.Append("where [free_id]=@id ");
            SqlParameter[] parameters = (SqlParameter[])this.ValueParams(model);
            ChangeHope.DataBase.SQLServerHelper.ExecuteSql(strSql.ToString(),parameters);
        }

        public void Delete(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from [yxs_freelable] where ");
            strSql.Append("[free_id]="+id );
            ChangeHope.DataBase.SQLServerHelper.ExecuteSql(strSql.ToString());
        }

        public void DeleteAll(string ids)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from [yxs_freelable] where ");
            strSql.Append("[free_id] in( "+ids+" )");
            ChangeHope.DataBase.SQLServerHelper.ExecuteSql(strSql.ToString());
        }
        #endregion

        #region Data Load
        /// <summary>
        /// 分页
        /// </summary>
        /// <returns></returns>
        public ChangeHope.DataBase.DataByPage GetListAll()
        {
            string sql = "[select] * [from] yxs_freelable [where] 1=1 [order by] free_id desc";
            ChangeHope.DataBase.DataByPage dataPage = new ChangeHope.DataBase.DataByPage();
            dataPage.Sql = sql;
            dataPage.GetRecordSetByPage();
            return dataPage;
        }
        /// <summary>
        /// 分页排序所有记录
        /// </summary>
        /// <remarks></remarks>
        public ChangeHope.DataBase.DataByPage GetListAll(string sql, int pagesize)
        {
            ChangeHope.DataBase.DataByPage dataPage = new ChangeHope.DataBase.DataByPage();
            dataPage.Sql =sql;
            dataPage.PageSize = pagesize;
            dataPage.GetRecordFreelabelByPage();
            return dataPage;
        }
        /// <summary>
        /// 根据条件返回集合
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public List<YXShop.Model.Templates.FreeLabel> GetListByWhere(string where)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from [yxs_freelable] ");
            if(where!=null&&where!=""){
                strSql.Append(" where "+where+" ");
            }
            List<YXShop.Model.Templates.FreeLabel> list = new List<YXShop.Model.Templates.FreeLabel>();
            using(SqlDataReader reader=ChangeHope.DataBase.SQLServerHelper.ExecuteReader(strSql.ToString()))
            {
                while(reader.Read())
                {
                    YXShop.Model.Templates.FreeLabel model = new YXShop.Model.Templates.FreeLabel();
                    model.Id = (int)reader["free_id"];
                    model.TypeId = (int)reader["type_id"];
                    model.LabelName = (string)reader["free_name"];
                    model.StyleContent = (string)reader["free_content"];
                    model.Description = (string)reader["free_description"];
                    model.CreateTime = Convert.ToDateTime(reader["free_createtime"]);
                    model.LabelType = Convert.ToInt32(reader["free_labeltype"]);
                    model.LabelClass = (int)reader["free_labelclass"];
                    model.LabelSql = (string)reader["free_labelsql"];
                    list.Add(model);
                }
            }
            return list;
        }
 
        /// <summary>
        /// 查所有表名
        /// </summary>
        /// <returns></returns>
        public IList<YXShop.Model.Templates.FreeLabel.FreeLablelDBInfo> GetTables()
        {
            IList<YXShop.Model.Templates.FreeLabel.FreeLablelDBInfo> tables = new List<YXShop.Model.Templates.FreeLabel.FreeLablelDBInfo>();
            DataTable dt = ChangeHope.DataBase.DataHelper.GetDataTable();     
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (dt.Rows[i].ItemArray[3].ToString() == "BASE TABLE")
                {
                    string TabNm = dt.Rows[i][2].ToString();
                    if (!TabNm.ToLower().Equals("dtproperties"))
                        tables.Add(new YXShop.Model.Templates.FreeLabel.FreeLablelDBInfo(TabNm, TabNm, ""));
                }
            }
            return tables;
        }
        /// <summary>
        /// 查表字段类型
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public IList<YXShop.Model.Templates.FreeLabel.FreeLablelDBInfo> GetFields(string tableName)
        {
            IList<YXShop.Model.Templates.FreeLabel.FreeLablelDBInfo> fields = new List<YXShop.Model.Templates.FreeLabel.FreeLablelDBInfo>();
            string sql = "select top 1 * from " + tableName + " where 1=0";
            IDataReader rd = ChangeHope.DataBase.SQLServerHelper.ExecuteReader(sql);
            for (int i = 0; i < rd.FieldCount; i++)
            {
                string fdnm = rd.GetName(i);
                fields.Add(new YXShop.Model.Templates.FreeLabel.FreeLablelDBInfo(fdnm, fdnm, rd.GetDataTypeName(i)));
            }
            if (rd.IsClosed == false)
                rd.Close();
            return fields;
        }
        /// <summary>
        /// 根据ID查对象
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public YXShop.Model.Templates.FreeLabel GetSingle(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select free_id,type_id,free_name,free_content,free_description,free_createtime,free_labeltype,free_labelclass,free_labelsql from [yxs_freelable] where free_id="+id);
            YXShop.Model.Templates.FreeLabel model = null;
            using(SqlDataReader reader=ChangeHope.DataBase.SQLServerHelper.ExecuteReader(strSql.ToString()))
            {
                if(reader.Read()){
                    model = new YXShop.Model.Templates.FreeLabel();
                    model.Id = (int)reader["free_id"];
                    model.TypeId = (int)reader["type_id"];
                    model.LabelName = (string)reader["free_name"];
                    model.StyleContent = (string)reader["free_content"];
                    model.Description = (string)reader["free_description"];
                    model.CreateTime = Convert.ToDateTime(reader["free_createtime"]);
                    model.LabelType = Convert.ToInt32(reader["free_labeltype"]);
                    model.LabelClass = (int)reader["free_labelclass"];
                    model.LabelSql = (string)reader["free_labelsql"];
                }
            }
            return model;
        }

        /// <summary>
        /// 是否存在该标签
        /// </summary>
        /// <param name="Name"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool IsRepeat(string Name, int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(*) from [yxs_freelable] where free_name=@free_name");
            if (id > 0)
                strSql.Append(" and free_id <> " + id);
            SqlParameter param = new SqlParameter("@free_name", SqlDbType.VarChar, 50);
            param.Value = Name;
            int n = Convert.ToInt32(ChangeHope.DataBase.SQLServerHelper.GetSingle(strSql.ToString(),param));
            if (n > 0)
                return true;
            else
                return false;

        }
        #endregion

        #region Other function
        /// <summary>
        /// 设置参数
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public IDbDataParameter[] ValueParams(YXShop.Model.Templates.FreeLabel model)
        {
            SqlParameter[] parameters=new SqlParameter[9];
            parameters[0] = new SqlParameter("@id",SqlDbType.Int,4);
            parameters[1] = new SqlParameter("@type_id",SqlDbType.Int,4);
            parameters[2] = new SqlParameter("@free_name",SqlDbType.VarChar,50);
            parameters[3] = new SqlParameter("@free_description",SqlDbType.VarChar,200);
            parameters[4] = new SqlParameter("@free_createtime",SqlDbType.DateTime,8);
            parameters[5] = new SqlParameter("@free_labeltype",SqlDbType.Int,4);
            parameters[6] = new SqlParameter("@free_labelclass",SqlDbType.Int,4);
            parameters[7] = new SqlParameter("@free_labelsql",SqlDbType.VarChar,500);
            parameters[8] = new SqlParameter("@free_content",SqlDbType.Text);
            parameters[0].Value = model.Id;
            parameters[1].Value = model.TypeId;
            parameters[2].Value = model.LabelName;
            parameters[3].Value = model.Description;
            parameters[4].Value = model.CreateTime;
            parameters[5].Value = model.LabelType;
            parameters[6].Value = model.LabelClass;
            parameters[7].Value = model.LabelSql;
            parameters[8].Value = model.StyleContent;
            return parameters;
        }
        #endregion
    }
}
